package com.imooc.points.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.imooc.commons.model.pojo.DinerPoints;
import com.imooc.commons.model.vo.DinerPointsRankVO;

/**
 * \* @author: bear
 * \* Date: 2021-08-17-10:45
 * \* describe: 积分mapper
 */
public interface DinerPointsMapper {

	//添加积分
	@Insert("insert into t_diner_points (fk_diner_id, points, types, is_valid, create_date, update_date) "
			+ " values (#{fkDinerId}, #{points}, #{types}, 1, now(), now())")
	void save(DinerPoints dinerPoints);

	//查询积分排行榜
	@Select("SELECT fk_diner_id as id,sum(points) as total, "
			+ "rank() over(order by sum(points) desc) as ranks "
			+ "FROM `t_diner_points` group by fk_diner_id "
			+ "order by total desc limit #{top}")
	List<DinerPointsRankVO> findTopN(@Param("top")int top);

	//根据食客id查询当前食客的积分排名
	@Select("SELECT id,total,ranks FROM(" +
			  "SELECT fk_diner_id AS id,sum( points ) AS total,rank() over ( ORDER BY sum( points ) DESC ) AS ranks "
			+ "FROM t_diner_points GROUP BY fk_diner_id  ORDER BY total DESC  ) r "
			+ "WHERE " + "id = #{dinerId}")
	DinerPointsRankVO findDinerRank(@Param("dinerId") int dinerId);
}
